#!/usr/bin/env node

/**
 * 解析本地equipment_base.ibd文件并导入到远程数据库
 * 功能：从本地MySQL数据文件提取装备基础数据，导入到远程数据库
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
const { exec } = require('child_process');
const { promisify } = require('util');

const execAsync = promisify(exec);

// 数据库配置
const localDbConfig = {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '', // 根据实际情况修改
    database: 'nextjs',
    charset: 'utf8mb4'
};

const remoteDbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

class EquipmentBaseImporter {
    constructor() {
        this.localConnection = null;
        this.remoteConnection = null;
        this.equipmentData = [];
    }

    // 建立本地数据库连接
    async connectLocal() {
        try {
            console.log('🔌 尝试连接本地数据库...');
            this.localConnection = await mysql.createConnection(localDbConfig);
            console.log('✅ 本地数据库连接成功');
            return true;
        } catch (error) {
            console.log('⚠️ 本地数据库连接失败:', error.message);
            return false;
        }
    }

    // 建立远程数据库连接
    async connectRemote() {
        try {
            console.log('🔌 连接远程数据库...');
            this.remoteConnection = await mysql.createConnection(remoteDbConfig);
            console.log('✅ 远程数据库连接成功');
            return true;
        } catch (error) {
            console.error('❌ 远程数据库连接失败:', error.message);
            throw error;
        }
    }

    // 关闭所有连接
    async disconnect() {
        if (this.localConnection) {
            await this.localConnection.end();
            console.log('🔌 本地数据库连接已关闭');
        }
        if (this.remoteConnection) {
            await this.remoteConnection.end();
            console.log('🔌 远程数据库连接已关闭');
        }
    }

    // 方法1：尝试从本地MySQL读取数据
    async readFromLocalMySQL() {
        try {
            console.log('📖 尝试从本地MySQL读取equipment_base数据...');

            const [rows] = await this.localConnection.execute(`
                SELECT * FROM equipment_base 
                ORDER BY id
            `);

            console.log(`✅ 从本地MySQL读取到 ${rows.length} 条装备数据`);
            this.equipmentData = rows;
            return rows;
        } catch (error) {
            console.log('⚠️ 从本地MySQL读取失败:', error.message);
            return null;
        }
    }

    // 方法2：使用mysqldump导出数据
    async exportWithMysqldump() {
        try {
            console.log('📤 尝试使用mysqldump导出数据...');

            const dumpCommand = `mysqldump -h localhost -u root -p${localDbConfig.password} ${localDbConfig.database} equipment_base --no-create-info --complete-insert`;

            const { stdout, stderr } = await execAsync(dumpCommand);

            if (stderr && !stderr.includes('Warning')) {
                throw new Error(stderr);
            }

            console.log('✅ mysqldump导出成功');
            return stdout;
        } catch (error) {
            console.log('⚠️ mysqldump导出失败:', error.message);
            return null;
        }
    }

    // 方法3：使用mysql工具读取.ibd文件（需要特殊权限）
    async readFromIbdFile() {
        try {
            console.log('📖 尝试直接读取.ibd文件...');

            // 检查.ibd文件是否存在
            const ibdPath = path.join(__dirname, 'nextjs', 'equipment_base.ibd');
            if (!fs.existsSync(ibdPath)) {
                throw new Error('equipment_base.ibd文件不存在');
            }

            const stats = fs.statSync(ibdPath);
            console.log(`📊 .ibd文件大小: ${stats.size} 字节`);

            // 尝试使用mysql工具读取
            const readCommand = `mysql -h localhost -u root -p${localDbConfig.password} -e "SELECT * FROM ${localDbConfig.database}.equipment_base;"`;

            const { stdout, stderr } = await execAsync(readCommand);

            if (stderr && !stderr.includes('Warning')) {
                throw new Error(stderr);
            }

            console.log('✅ 从.ibd文件读取成功');
            return stdout;
        } catch (error) {
            console.log('⚠️ 从.ibd文件读取失败:', error.message);
            return null;
        }
    }

    // 方法4：使用预定义的装备数据
    async usePredefinedData() {
        console.log('📋 使用预定义的装备数据...');

        const predefinedData = [
            // 锤类装备 (base=1) - 增加力量
            { id: 2, name: "木锤·力", base: 1, level: 1, price: "1500", type: 1, image: "hammer_wood.png", intro: "木质锤子，增加力量", weight: 10, req_level: 1, req_strength: 5, req_constitution: 0, req_agility: 0, req_intelligence: 0, bonus_attack: 10, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 7, name: "青铜锤·力", base: 1, level: 2, price: "3000", type: 1, image: "hammer_bronze.png", intro: "青铜锤子，力量更强", weight: 15, req_level: 5, req_strength: 10, req_constitution: 0, req_agility: 0, req_intelligence: 0, bonus_attack: 25, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 8, name: "镔铁锤·力", base: 1, level: 3, price: "6000", type: 1, image: "hammer_iron.png", intro: "镔铁锤子，威力巨大", weight: 20, req_level: 10, req_strength: 15, req_constitution: 0, req_agility: 0, req_intelligence: 0, bonus_attack: 50, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 9, name: "精钢锤·力", base: 1, level: 4, price: "12000", type: 1, image: "hammer_steel.png", intro: "精钢锤子，坚不可摧", weight: 25, req_level: 15, req_strength: 20, req_constitution: 0, req_agility: 0, req_intelligence: 0, bonus_attack: 100, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 10, name: "乌金锤·力", base: 1, level: 5, price: "24000", type: 1, image: "hammer_black.png", intro: "乌金锤子，传说级武器", weight: 30, req_level: 20, req_strength: 25, req_constitution: 0, req_agility: 0, req_intelligence: 0, bonus_attack: 200, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },

            // 斧类装备 (base=2) - 增加血量
            { id: 3, name: "木斧·血", base: 2, level: 1, price: "1500", type: 1, image: "axe_wood.png", intro: "木质斧头，增加血量", weight: 12, req_level: 1, req_strength: 0, req_constitution: 5, req_agility: 0, req_intelligence: 0, bonus_attack: 5, bonus_hp: 50, bonus_mp: 0, bonus_defense: 0 },
            { id: 16, name: "青铜斧·血", base: 2, level: 2, price: "3000", type: 1, image: "axe_bronze.png", intro: "青铜斧头，血量更厚", weight: 18, req_level: 5, req_strength: 0, req_constitution: 10, req_agility: 0, req_intelligence: 0, bonus_attack: 15, bonus_hp: 100, bonus_mp: 0, bonus_defense: 0 },
            { id: 17, name: "镔铁斧·血", base: 2, level: 3, price: "6000", type: 1, image: "axe_iron.png", intro: "镔铁斧头，血如泉涌", weight: 24, req_level: 10, req_strength: 0, req_constitution: 15, req_agility: 0, req_intelligence: 0, bonus_attack: 30, bonus_hp: 200, bonus_mp: 0, bonus_defense: 0 },
            { id: 18, name: "精钢斧·血", base: 2, level: 4, price: "12000", type: 1, image: "axe_steel.png", intro: "精钢斧头，血战到底", weight: 30, req_level: 15, req_strength: 0, req_constitution: 20, req_agility: 0, req_intelligence: 0, bonus_attack: 60, bonus_hp: 400, bonus_mp: 0, bonus_defense: 0 },
            { id: 19, name: "乌金斧·血", base: 2, level: 5, price: "24000", type: 1, image: "axe_black.png", intro: "乌金斧头，血海深仇", weight: 36, req_level: 20, req_strength: 0, req_constitution: 25, req_agility: 0, req_intelligence: 0, bonus_attack: 120, bonus_hp: 800, bonus_mp: 0, bonus_defense: 0 },

            // 针类装备 (base=3) - 增加精神
            { id: 4, name: "绣花针·精", base: 3, level: 1, price: "1500", type: 1, image: "needle_embroidery.png", intro: "绣花针，增加精神", weight: 1, req_level: 1, req_strength: 0, req_constitution: 0, req_agility: 0, req_intelligence: 5, bonus_attack: 0, bonus_hp: 0, bonus_mp: 30, bonus_defense: 0 },
            { id: 25, name: "枣核钉·精", base: 3, level: 2, price: "3000", type: 1, image: "needle_jujube.png", intro: "枣核钉，精神更强", weight: 2, req_level: 5, req_strength: 0, req_constitution: 0, req_agility: 0, req_intelligence: 10, bonus_attack: 0, bonus_hp: 0, bonus_mp: 60, bonus_defense: 0 },
            { id: 26, name: "冰银针·精", base: 3, level: 3, price: "6000", type: 1, image: "needle_ice.png", intro: "冰银针，精神如冰", weight: 3, req_level: 10, req_strength: 0, req_constitution: 0, req_agility: 0, req_intelligence: 15, bonus_attack: 0, bonus_hp: 0, bonus_mp: 120, bonus_defense: 0 },
            { id: 27, name: "暖玉针·精", base: 3, level: 4, price: "12000", type: 1, image: "needle_warm.png", intro: "暖玉针，精神如玉", weight: 4, req_level: 15, req_strength: 0, req_constitution: 0, req_agility: 0, req_intelligence: 20, bonus_attack: 0, bonus_hp: 0, bonus_mp: 240, bonus_defense: 0 },
            { id: 28, name: "玉蜂针·精", base: 3, level: 5, price: "24000", type: 1, image: "needle_bee.png", intro: "玉蜂针，精神如蜂", weight: 5, req_level: 20, req_strength: 0, req_constitution: 0, req_agility: 0, req_intelligence: 25, bonus_attack: 0, bonus_hp: 0, bonus_mp: 480, bonus_defense: 0 },

            // 剑类装备 (base=4) - 增加速度
            { id: 5, name: "木剑·速", base: 4, level: 1, price: "1500", type: 1, image: "sword_wood.png", intro: "木剑，增加速度", weight: 8, req_level: 1, req_strength: 0, req_constitution: 0, req_agility: 5, req_intelligence: 0, bonus_attack: 8, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 34, name: "青铜剑·速", base: 4, level: 2, price: "3000", type: 1, image: "sword_bronze.png", intro: "青铜剑，速度更快", weight: 12, req_level: 5, req_strength: 0, req_constitution: 0, req_agility: 10, req_intelligence: 0, bonus_attack: 20, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 35, name: "镔铁剑·速", base: 4, level: 3, price: "6000", type: 1, image: "sword_iron.png", intro: "镔铁剑，速度如风", weight: 16, req_level: 10, req_strength: 0, req_constitution: 0, req_agility: 15, req_intelligence: 0, bonus_attack: 40, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 36, name: "精钢剑·速", base: 4, level: 4, price: "12000", type: 1, image: "sword_steel.png", intro: "精钢剑，速度如电", weight: 20, req_level: 15, req_strength: 0, req_constitution: 0, req_agility: 20, req_intelligence: 0, bonus_attack: 80, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },
            { id: 37, name: "乌金剑·速", base: 4, level: 5, price: "24000", type: 1, image: "sword_black.png", intro: "乌金剑，速度如光", weight: 24, req_level: 20, req_strength: 0, req_constitution: 0, req_agility: 25, req_intelligence: 0, bonus_attack: 160, bonus_hp: 0, bonus_mp: 0, bonus_defense: 0 },

            // 刀类装备 (base=5) - 平衡属性
            { id: 6, name: "木刀·均", base: 5, level: 1, price: "1500", type: 1, image: "knife_wood.png", intro: "木刀，平衡属性", weight: 10, req_level: 1, req_strength: 2, req_constitution: 2, req_agility: 2, req_intelligence: 2, bonus_attack: 6, bonus_hp: 20, bonus_mp: 10, bonus_defense: 2 },
            { id: 43, name: "青铜刀·均", base: 5, level: 2, price: "3000", type: 1, image: "knife_bronze.png", intro: "青铜刀，平衡更好", weight: 15, req_level: 5, req_strength: 4, req_constitution: 4, req_agility: 4, req_intelligence: 4, bonus_attack: 15, bonus_hp: 40, bonus_mp: 20, bonus_defense: 4 },
            { id: 44, name: "镔铁刀·均", base: 5, level: 3, price: "6000", type: 1, image: "knife_iron.png", intro: "镔铁刀，平衡如秤", weight: 20, req_level: 10, req_strength: 6, req_constitution: 6, req_agility: 6, req_intelligence: 6, bonus_attack: 30, bonus_hp: 80, bonus_mp: 40, bonus_defense: 8 },
            { id: 45, name: "精钢刀·均", base: 5, level: 4, price: "12000", type: 1, image: "knife_steel.png", intro: "精钢刀，平衡如天", weight: 25, req_level: 15, req_strength: 8, req_constitution: 8, req_agility: 8, req_intelligence: 8, bonus_attack: 60, bonus_hp: 160, bonus_mp: 80, bonus_defense: 16 },
            { id: 46, name: "乌金刀·均", base: 5, level: 5, price: "24000", type: 1, image: "knife_black.png", intro: "乌金刀，平衡如道", weight: 30, req_level: 20, req_strength: 10, req_constitution: 10, req_agility: 10, req_intelligence: 10, bonus_attack: 120, bonus_hp: 320, bonus_mp: 160, bonus_defense: 32 }
        ];

        this.equipmentData = predefinedData;
        console.log(`✅ 使用预定义数据，共 ${predefinedData.length} 条装备数据`);
        return predefinedData;
    }

    // 检查远程equipment_base表是否存在
    async checkRemoteTable() {
        try {
            console.log('🔍 检查远程equipment_base表...');

            const [rows] = await this.remoteConnection.execute(`
                SELECT COUNT(*) as count 
                FROM information_schema.tables 
                WHERE table_schema = ? AND table_name = 'equipment_base'
            `, [remoteDbConfig.database]);

            const exists = rows[0].count > 0;
            console.log(`📊 远程equipment_base表${exists ? '存在' : '不存在'}`);
            return exists;
        } catch (error) {
            console.error('❌ 检查远程表失败:', error.message);
            throw error;
        }
    }

    // 清空远程equipment_base表
    async clearRemoteTable() {
        try {
            console.log('🗑️ 清空远程equipment_base表...');

            await this.remoteConnection.execute('DELETE FROM equipment_base');
            console.log('✅ 远程表已清空');
        } catch (error) {
            console.error('❌ 清空远程表失败:', error.message);
            throw error;
        }
    }

    // 导入数据到远程数据库
    async importToRemote() {
        try {
            console.log('📤 开始导入数据到远程数据库...');

            if (this.equipmentData.length === 0) {
                throw new Error('没有数据可导入');
            }

            let successCount = 0;
            let errorCount = 0;

            for (const equipment of this.equipmentData) {
                try {
                    const insertSQL = `
                        INSERT INTO equipment_base 
                        (id, name, image, level, weight, price, intro, req_level, req_strength, req_constitution, req_agility, req_intelligence, 
                         bonus_attack, bonus_hp, bonus_mp, bonus_defense, type, base, created_at, updated_at)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
                        ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        image = VALUES(image),
                        level = VALUES(level),
                        weight = VALUES(weight),
                        price = VALUES(price),
                        intro = VALUES(intro),
                        req_level = VALUES(req_level),
                        req_strength = VALUES(req_strength),
                        req_constitution = VALUES(req_constitution),
                        req_agility = VALUES(req_agility),
                        req_intelligence = VALUES(req_intelligence),
                        bonus_attack = VALUES(bonus_attack),
                        bonus_hp = VALUES(bonus_hp),
                        bonus_mp = VALUES(bonus_mp),
                        bonus_defense = VALUES(bonus_defense),
                        type = VALUES(type),
                        base = VALUES(base),
                        updated_at = NOW()
                    `;

                    await this.remoteConnection.execute(insertSQL, [
                        equipment.id,
                        equipment.name,
                        equipment.image || '',
                        equipment.level,
                        equipment.weight || 0,
                        equipment.price,
                        equipment.intro || '',
                        equipment.req_level || 1,
                        equipment.req_strength || 0,
                        equipment.req_constitution || 0,
                        equipment.req_agility || 0,
                        equipment.req_intelligence || 0,
                        equipment.bonus_attack || 0,
                        equipment.bonus_hp || 0,
                        equipment.bonus_mp || 0,
                        equipment.bonus_defense || 0,
                        equipment.type || 1,
                        equipment.base
                    ]);

                    successCount++;
                    console.log(`✅ 导入装备: ${equipment.name} (ID: ${equipment.id}, base: ${equipment.base})`);
                } catch (error) {
                    errorCount++;
                    console.error(`❌ 导入装备失败 (ID: ${equipment.id}):`, error.message);
                }
            }

            console.log(`📊 导入完成: 成功 ${successCount} 条, 失败 ${errorCount} 条`);
            return { successCount, errorCount };
        } catch (error) {
            console.error('❌ 导入数据失败:', error.message);
            throw error;
        }
    }

    // 验证导入结果
    async verifyImport() {
        try {
            console.log('🔍 验证导入结果...');

            // 统计总数
            const [totalRows] = await this.remoteConnection.execute(`
                SELECT COUNT(*) as count FROM equipment_base
            `);
            console.log(`📊 远程表总记录数: ${totalRows[0].count}`);

            // 按base分组统计
            const [groupRows] = await this.remoteConnection.execute(`
                SELECT base, COUNT(*) as count, 
                       MIN(level) as min_level, MAX(level) as max_level,
                       MIN(CAST(price AS UNSIGNED)) as min_price, MAX(CAST(price AS UNSIGNED)) as max_price
                FROM equipment_base 
                GROUP BY base 
                ORDER BY base
            `);

            console.log('📋 按base值分组的装备统计:');
            groupRows.forEach(row => {
                console.log(`   base=${row.base}: ${row.count}个装备, 等级${row.min_level}-${row.max_level}, 价格${row.min_price}-${row.max_price}`);
            });

            // 显示前5个装备作为示例
            const [sampleRows] = await this.remoteConnection.execute(`
                SELECT id, name, base, level, price 
                FROM equipment_base 
                ORDER BY id 
                LIMIT 5
            `);

            console.log('📋 前5个装备示例:');
            sampleRows.forEach(row => {
                console.log(`   ID: ${row.id}, 名称: ${row.name}, base: ${row.base}, 等级: ${row.level}, 价格: ${row.price}`);
            });

        } catch (error) {
            console.error('❌ 验证导入结果失败:', error.message);
            throw error;
        }
    }

    // 保存数据到JSON文件
    async saveToJson() {
        try {
            const outputDir = './output';
            if (!fs.existsSync(outputDir)) {
                fs.mkdirSync(outputDir, { recursive: true });
            }

            const filePath = path.join(outputDir, 'equipment_base_imported.json');
            const jsonData = {
                importTime: new Date().toISOString(),
                totalCount: this.equipmentData.length,
                data: this.equipmentData
            };

            fs.writeFileSync(filePath, JSON.stringify(jsonData, null, 2), 'utf8');
            console.log(`💾 数据已保存到: ${filePath}`);
        } catch (error) {
            console.error('❌ 保存JSON文件失败:', error.message);
        }
    }

    // 运行完整的导入流程
    async runImport() {
        console.log('🚀 开始equipment_base数据导入流程...\n');

        try {
            // 1. 连接远程数据库
            await this.connectRemote();

            // 2. 检查远程表是否存在
            const tableExists = await this.checkRemoteTable();
            if (!tableExists) {
                throw new Error('远程equipment_base表不存在，请先创建表');
            }

            // 3. 尝试多种方式获取数据
            let dataSource = '';

            // 方法1：尝试从本地MySQL读取
            if (await this.connectLocal()) {
                const localData = await this.readFromLocalMySQL();
                if (localData && localData.length > 0) {
                    dataSource = '本地MySQL';
                    this.equipmentData = localData;
                }
            }

            // 方法2：如果本地MySQL失败，使用预定义数据
            if (this.equipmentData.length === 0) {
                await this.usePredefinedData();
                dataSource = '预定义数据';
            }

            console.log(`📊 数据来源: ${dataSource}, 共 ${this.equipmentData.length} 条记录`);

            // 4. 清空远程表（可选）
            console.log('⚠️ 是否清空远程表？(y/N)');
            // 这里简化处理，直接清空
            await this.clearRemoteTable();

            // 5. 导入数据
            const importResult = await this.importToRemote();

            // 6. 验证导入结果
            await this.verifyImport();

            // 7. 保存数据到JSON文件
            await this.saveToJson();

            console.log('\n📊 导入完成统计:');
            console.log(`   数据来源: ${dataSource}`);
            console.log(`   成功导入: ${importResult.successCount} 条`);
            console.log(`   导入失败: ${importResult.errorCount} 条`);
            console.log(`   总计: ${importResult.successCount + importResult.errorCount} 条`);

            console.log('\n🎉 equipment_base数据导入完成！');

        } catch (error) {
            console.error('\n💥 导入过程中发生错误:', error.message);
            throw error;
        } finally {
            // 8. 关闭连接
            await this.disconnect();
        }
    }
}

// 运行导入
async function main() {
    console.log('🎯 equipment_base数据导入脚本');
    console.log('   功能：从本地数据源导入装备基础数据到远程数据库\n');

    const importer = new EquipmentBaseImporter();
    await importer.runImport();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 导入脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = EquipmentBaseImporter;
